package com.cupk.repository;

import com.cupk.entity.Movie;
import com.cupk.entity.User;
import com.cupk.util.JDBCTools;
import com.cupk.util.StringTools;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * @author LY
 * @version 1.0
 */
public class UserRepository {
    //连接池
    private static ComboPooledDataSource dataSource;

    static {
        dataSource = new ComboPooledDataSource("testc3p0");
    }


    public UserRepository() {
    }

    //登录
    public User login(String username, String password) {
        Connection connection = JDBCTools.getConnection();
        String sql = "select * from userlist where username = ? and password = ?";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        User user = null;
        try {
            statement = connection.prepareStatement(sql);
            statement.setString(1, username);
            statement.setString(2, password);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                user = new User(resultSet.getInt(1), resultSet.getString(2), resultSet.getString(3), resultSet.getString(4), resultSet.getString(5), resultSet.getString(6));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCTools.release(connection, statement, resultSet);
        }
        return user;
    }

    //获取所有用户列表
    public List<User> findAll() {
        Connection connection = null;
        List<User> list = new ArrayList<>();
        try {
            connection = dataSource.getConnection();
            String sql = "select * from userlist ";
            QueryRunner queryRunner = new QueryRunner();
            list = queryRunner.query(connection, sql, new BeanListHandler<>(User.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return list;
    }

//    //根据用户id影片id查找相关记录
//    public String recordSearch(Integer userId, Integer id){
//        Connection connection = JDBCTools.getConnection();
//        String sql = "select records from userlist where id = ?";
//        PreparedStatement statement = null;
//        ResultSet resultSet = null;
//        User user = null;
//        String records = null;
//        try {
//            statement = connection.prepareStatement(sql);
//            statement.setInt(1, userId);
//            resultSet = statement.executeQuery();
//            if (resultSet.next()) {
//                records = resultSet.getString(1);
//                if(records == null)
//                    return records;
//                HashMap<Integer,Integer> newRecord = StringTools.func(records);
//                if(newRecord.get(id) == null){
//                    records = records + "+";
//                }else {
//                    records = records + newRecord.get(id);
//                }
//
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
//        } finally {
//            JDBCTools.release(connection, statement, resultSet);
//        }
//        return records;
//    }

    //更新用户评分
    public void renewRecords(String records, Integer id){
        Connection connection = JDBCTools.getConnection();
        String sql = "UPDATE userlist SET records = ? WHERE id = ?;";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,records);
            preparedStatement.setInt(2,id);
            preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCTools.release(connection, preparedStatement, null);
        }
    }


    //根据id返回相关用户
    public User findById(Integer id) {
        Connection connection = null;
        User user = null;
        try {
            connection = dataSource.getConnection();
            String sql = "select * from userlist where id = " +"\""+id+"\"";
            QueryRunner queryRunner = new QueryRunner();
            user = queryRunner.query(connection,sql,new BeanHandler<>(User.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return user;
    }

    //添加新的收藏记录
    public void addCollectible(Integer userId,String newCollect){
        Connection connection = JDBCTools.getConnection();
        String sql = "UPDATE userlist SET collectible = ? WHERE id = ?;";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,newCollect);
            preparedStatement.setInt(2,userId);
            preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCTools.release(connection, preparedStatement, null);
        }
    }

    //注册用户
    public void register(String username,String password,String hobbies){
        Connection connection = JDBCTools.getConnection();
        String sql = "INSERT INTO userlist(username,password,hobbies) VALUES(?, ?, ?);";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,username);
            preparedStatement.setString(2,password);
            preparedStatement.setString(3,hobbies);
            int result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCTools.release(connection, preparedStatement, null);
        }
    }

    //修改个人信息
    public void editInformation(Integer userId,String username,String password,String hobbies){
        Connection connection = JDBCTools.getConnection();
        String sql = "UPDATE userlist SET username = ?, password = ?, hobbies = ? WHERE id = ?;";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,username);
            preparedStatement.setString(2,password);
            preparedStatement.setString(3,hobbies);
            preparedStatement.setInt(4,userId);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCTools.release(connection, preparedStatement, null);
        }
    }

    //删除收藏记录
    public void deleteCollect(String collectible, Integer userId){
        Connection connection = JDBCTools.getConnection();
        String sql = null;
        if(collectible.equals("")){
            sql = "UPDATE userlist SET collectible = NULL WHERE id = ?;";
        }else {
            sql = "UPDATE userlist SET collectible = "+ "\"" +collectible + "\"" +  " WHERE id = ?;";
        }
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,userId);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCTools.release(connection, preparedStatement, null);
        }
    }
}
